from fastapi import APIRouter, HTTPException, Depends
from sqlalchemy import text

from database.database_function import get_db
from api.schemas.target_database import DatabaseInitRequest, DatabaseInitResponse
from target_database.database import MySQLQueryExecutor


router = APIRouter()


@router.post("/database_init", response_model=DatabaseInitResponse, summary="第一次连接数据库初始化并保存结构")
async def database_init(body: DatabaseInitRequest, db=Depends(get_db)):
    # 1) 连接目标数据库并测试
    executor = MySQLQueryExecutor(
        host=body.database_host,
        user=body.database_user,
        password=body.database_password,
        database=body.database_name,
        port=body.database_port,
    )

    if not executor.connect():
        raise HTTPException(status_code=400, detail="连接目标数据库失败，请检查配置")

    try:
        structure_md = executor.structure_to_markdown()
    finally:
        executor.close()

    # 3) 判断是否已存在记录（按 host+port+user+db 唯一）
    select_sql = text(
        """
        SELECT id FROM database_info
        WHERE database_host = :host AND database_port = :port AND database_user = :user AND database_name = :db
        ORDER BY id DESC LIMIT 1
        """
    )
    row = db.execute(select_sql, {
        "host": body.database_host,
        "port": body.database_port,
        "user": body.database_user,
        "db": body.database_name,
    }).fetchone()

    if row:
        # 已存在：更新密码与结构
        update_sql = text(
            """
            UPDATE database_info
            SET database_password = :pwd, database_structure = :structure
            WHERE id = :id
            """
        )
        db.execute(update_sql, {
            "pwd": body.database_password,
            "structure": structure_md,
            "id": row[0]
        })
        db.commit()
        return DatabaseInitResponse(success=True, id=row[0], message="已存在记录，已更新结构")

    # 4) 插入新记录
    insert_sql = text(
        """
        INSERT INTO database_info (
            database_name, database_host, database_port, database_user, database_password, database_structure
        ) VALUES (
            :db, :host, :port, :user, :pwd, :structure
        )
        """
    )
    db.execute(insert_sql, {
        "db": body.database_name,
        "host": body.database_host,
        "port": body.database_port,
        "user": body.database_user,
        "pwd": body.database_password,
        "structure": structure_md,
    })
    db.commit()

    # 再查一次拿 id
    new_row = db.execute(select_sql, {
        "host": body.database_host,
        "port": body.database_port,
        "user": body.database_user,
        "db": body.database_name,
    }).fetchone()

    return DatabaseInitResponse(success=True, id=int(new_row[0]) if new_row else None, message="连接成功并已保存")